<?php
require_once 'mysqlDriver.php';

// get parameters from the url
$_code = $_GET['code'];
$_maxDistance = $_GET['dist'];
$_ds = $_GET['ds'];
$_sw_lat = $_GET['sw_lat'];
$_sw_lng = $_GET['sw_lng'];
$_ne_lat = $_GET['ne_lat'];
$_ne_lng = $_GET['ne_lng'];

// empty_json:
//
// represents an empty json string
function empty_json() {
    // default json error/empty message
    echo "[{value: {name: \"empty\"}}]";
}

if(!empty($_ds)) {
    if(empty($_maxDistance) && empty($_code)) {
        $_array = array();
        // perform a rectangular window search for the faa web service tags
        if($_ds == "faaws") {
            $_sql = "select * from `faa_airport_loc_list` where " .
                    " `latitude` > $_sw_lat and `latitude` < $_ne_lat and" .
                    " `longitude` > $_sw_lng and `longitude` < $_ne_lng";
            $_result = sqlSelect($_sql);
            $_numRows = mysql_num_rows($_result);

            // format result into a php array
            if($_numRows > 0)
                while($_row = mysql_fetch_assoc($_result)) {
                    $_arrayRow["value"]["name"] = $_row["name"];
                    $_arrayRow["value"]["city"] = $_row["city"];
                    $_arrayRow["value"]["state"] = $_row["state"];
                    $_arrayRow["value"]["code"] = $_row["code"];
                    $_arrayRow["id"]["latitude"] = $_row["latitude"];
                    $_arrayRow["id"]["longitude"] = $_row["longitude"];
                    array_push($_array, $_arrayRow);
                }
        }
        // perform a rectangular window search for the usgs web service tags
        if($_ds == "usgs") {
             $_sql = "select * from `usgs_site_loc_list` where " .
                    " `latitude` > $_sw_lat and `latitude` < $_ne_lat and" .
                    " `longitude` > $_sw_lng and `longitude` < $_ne_lng";
            $_result = sqlSelect($_sql);
            $_numRows = mysql_num_rows($_result);

            // format result into a php array
            if($_numRows > 0)
                while($_row = mysql_fetch_assoc($_result)) {
                    $_arrayRow["value"]["name"] = $_row["site_name"];
                    $_arrayRow["value"]["code"] = $_row["site_id"];
                    $_arrayRow["id"]["latitude"] = $_row["latitude"];
                    $_arrayRow["id"]["longitude"] = $_row["longitude"];
                    array_push($_array, $_arrayRow);
                }
        }
        mysql_free_result($_result);
        echo json_encode($_array);
    }
    // an addition to allow traditional proximity searching to the USGS dataset
    elseif($_ds == "usgs") {
      // execute the majority of script logic only if the parameters are valid
    if($_code != null && $_maxDistance > 0 && $_maxDistance < 150) {
        // get information on the originating airport
        $_result = sqlSelect("SELECT longitude, latitude FROM `usgs_site_loc_list`" .
                " where site_id like '%" . $_code . "%' LIMIT 1");
        $_numRows = mysql_num_rows($_result);
        while($_row = mysql_fetch_assoc($_result)) {
            $_originLatitude = $_row['latitude'];
            $_originLongitude = $_row['longitude'];
        }
        mysql_free_result($_result);

        // perform the rest of the search only if
        if($_numRows > 0) {
            // perform a geocode proximity search
            $_con = mysql_connect($_mysql_db_host, $_mysql_db_username, $_mysql_db_password);
            if(!$_con)
                die('Could not connect: ' . mysql_error());
            mysql_select_db("data_explorer", $_con);
            // mysql representation of basic haversine formula
            $_result = mysql_query("SELECT *, ( 3959 * acos( cos( radians(" . $_originLatitude .
                    ") ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(" .
                    $_originLongitude . ") ) + sin( radians(" . $_originLatitude .
                    ") ) * sin( radians( latitude ) ) ) ) AS distance " .
                    "FROM `usgs_site_loc_list` HAVING distance < " . $_maxDistance .
                    " ORDER BY distance LIMIT 0, 20");
            mysql_close($_con);

            // format result into a json array
            $_array = array();
            while($_row = mysql_fetch_assoc($_result)) {
	        $_arrayRow['value']['name'] = $_row['site_name'];
	        $_arrayRow['value']['code'] = $_row['site_id'];
	        $_arrayRow['id']['latitude'] = $_row['latitude'];
                $_arrayRow['id']['longitude'] = $_row['longitude'];
                $_arrayRow['id']['distance'] = round($_row['distance'], 2) . " miles";
                array_push($_array, $_arrayRow);
            }
            mysql_free_result($_result);
            // return the result array back
            echo json_encode($_array);
        } else
            empty_json();
    } else
        empty_json();
    }
    else {
    }
} else {
    // execute the majority of script logic only if the parameters are valid
    if($_code != null && $_maxDistance > 0 && $_maxDistance < 150) {
        // get information on the originating airport
        $_result = sqlSelect("SELECT longitude, latitude FROM `faa_airport_loc_list`" .
                " where code like '%" . $_code . "%' LIMIT 1");
        $_numRows = mysql_num_rows($_result);
        while($_row = mysql_fetch_assoc($_result)) {
            $_originLatitude = $_row['latitude'];
            $_originLongitude = $_row['longitude'];
        }
        mysql_free_result($_result);

        // perform the rest of the search only if
        if($_numRows > 0) {
            // perform a geocode proximity search
            $_con = mysql_connect($_mysql_db_host, $_mysql_db_username, $_mysql_db_password);
            if(!$_con)
                die('Could not connect: ' . mysql_error());
            mysql_select_db("data_explorer", $_con);
            // mysql representation of basic haversine formula
            $_result = mysql_query("SELECT *, ( 3959 * acos( cos( radians(" . $_originLatitude .
                    ") ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(" .
                    $_originLongitude . ") ) + sin( radians(" . $_originLatitude .
                    ") ) * sin( radians( latitude ) ) ) ) AS distance " .
                    "FROM `faa_airport_loc_list` HAVING distance < " . $_maxDistance .
                    " ORDER BY distance LIMIT 0, 20");
            mysql_close($_con);

            // format result into a json array
            $_array = array();
            while($_row = mysql_fetch_assoc($_result)) {
                $_arrayRow['value'] = $_row['code'] .
                ', ' . $_row['name'] .
                ', ' . $_row['city'] .
                ', ' . $_row['state'];

                $_arrayRow['id']['latitude'] = $_row['latitude'];
                $_arrayRow['id']['longitude'] = $_row['longitude'];
                $_arrayRow['id']['distance'] = round($_row['distance'], 2) . " miles";
                array_push($_array, $_arrayRow);
            }
            mysql_free_result($_result);
            // return the result array back
            echo json_encode($_array);
        } else
            empty_json();
    } else
        empty_json();
}
